package com.cinema.db;

import java.io.IOException;
import java.io.InputStream;
import java.nio.charset.StandardCharsets;
import java.sql.*;
import java.util.logging.Logger;
import java.util.logging.Level;

/**
 * 数据库连接管理器
 * 负责SQLite数据库的连接、初始化和关闭
 */
public class DatabaseManager {
    private static final Logger logger = Logger.getLogger(DatabaseManager.class.getName());
    private static final String DB_URL = "jdbc:sqlite:cinema.db";
    private static final String SCHEMA_FILE = "schema.sql";
    
    private static DatabaseManager instance;
    private Connection connection;
    
    private DatabaseManager() {
        try {
            // 显式加载SQLite驱动
            Class.forName("org.sqlite.JDBC");
        } catch (ClassNotFoundException e) {
            logger.log(Level.SEVERE, "SQLite驱动加载失败", e);
            throw new RuntimeException("SQLite驱动加载失败", e);
        }
        initializeDatabase();
    }
    
    public static synchronized DatabaseManager getInstance() {
        if (instance == null) {
            instance = new DatabaseManager();
        }
        return instance;
    }
    
    /**
     * 获取数据库连接
     */
    public Connection getConnection() throws SQLException {
        if (connection == null || connection.isClosed()) {
            connection = DriverManager.getConnection(DB_URL);
            connection.setAutoCommit(false); // 启用事务
        }
        return connection;
    }
    
    /**
     * 初始化数据库
     */
    private void initializeDatabase() {
        try (Connection conn = DriverManager.getConnection(DB_URL)) {
            logger.info("数据库连接成功");
            
            // 执行建表脚本
            executeSchemaScript(conn);
            
            logger.info("数据库初始化完成");
        } catch (SQLException e) {
            logger.log(Level.SEVERE, "数据库初始化失败", e);
            throw new RuntimeException("数据库初始化失败", e);
        }
    }
    
    /**
     * 执行建表脚本
     */
    private void executeSchemaScript(Connection conn) throws SQLException {
        try (InputStream is = getClass().getClassLoader().getResourceAsStream(SCHEMA_FILE)) {
            if (is == null) {
                throw new RuntimeException("找不到数据库建表脚本: " + SCHEMA_FILE);
            }
            
            String schema = new String(is.readAllBytes(), StandardCharsets.UTF_8);
            String[] statements = schema.split(";");
            
            try (Statement stmt = conn.createStatement()) {
                for (String statement : statements) {
                    statement = statement.trim();
                    if (!statement.isEmpty()) {
                        stmt.execute(statement);
                    }
                }
            }
            
            // SQLite在auto-commit模式下不需要手动提交
            logger.info("数据库表结构创建完成");
        } catch (IOException e) {
            logger.log(Level.SEVERE, "读取建表脚本失败", e);
            throw new RuntimeException("读取建表脚本失败", e);
        }
    }
    
    /**
     * 关闭数据库连接
     */
    public void closeConnection() {
        if (connection != null) {
            try {
                connection.close();
                logger.info("数据库连接已关闭");
            } catch (SQLException e) {
                logger.log(Level.WARNING, "关闭数据库连接失败", e);
            }
        }
    }
    
    /**
     * 测试数据库连接
     */
    public boolean testConnection() {
        try (Connection conn = getConnection()) {
            return conn.isValid(5);
        } catch (SQLException e) {
            logger.log(Level.WARNING, "数据库连接测试失败", e);
            return false;
        }
    }
    
    /**
     * 执行事务
     */
    public <T> T executeTransaction(DatabaseTransaction<T> transaction) throws SQLException {
        Connection conn = getConnection();
        try {
            T result = transaction.execute(conn);
            conn.commit();
            return result;
        } catch (Exception e) {
            conn.rollback();
            throw new SQLException("事务执行失败", e);
        }
    }
    
    /**
     * 执行更新操作
     */
    public int executeUpdate(String sql, Object... params) throws SQLException {
        return executeTransaction(conn -> {
            try (PreparedStatement stmt = conn.prepareStatement(sql)) {
                for (int i = 0; i < params.length; i++) {
                    stmt.setObject(i + 1, params[i]);
                }
                return stmt.executeUpdate();
            }
        });
    }
    
    /**
     * 执行查询操作
     */
    public ResultSet executeQuery(String sql, Object... params) throws SQLException {
        Connection conn = getConnection();
        PreparedStatement stmt = conn.prepareStatement(sql);
        for (int i = 0; i < params.length; i++) {
            stmt.setObject(i + 1, params[i]);
        }
        return stmt.executeQuery();
    }
    
    /**
     * 数据库事务接口
     */
    @FunctionalInterface
    public interface DatabaseTransaction<T> {
        T execute(Connection connection) throws SQLException;
    }
} 